#!/anaconda3/envs/FEALPy/bin python3.8
# -*- coding: utf-8 -*-
# File: sql_helper.py
# Author: Bryan SHEN
# E-mail: m18801919240_3@163.com
# Site: Shanghai, China
# Time: 2024/6/20 16:54
# File-Desp:


INQUIRY_SQL = """

    select
    
        distinct 
        t5124."label" as "trade_type",
        t_origin."151" as "inquiry_key",
        t_origin."1983" as "user_id",
        t_origin."product_id" as "product_id",
        t_origin.product_name,
        t_origin."153" as "product_sku_id",
        t180.product_sku_name,
        t_origin."154" as "product_level_id",
        t179.product_level_name,
        t179.big_product_level_name as "product_level_name_big",
        
        t_origin."4036" as "amount",
        t_origin."5857" as "date",
        t_origin."983" as "big_source_type_name",
        ods.property_values_by_manual as "manual_property",
        ods.sku_values as "sku_property",
        -- t179.product_level_name as "product_level_name",
        
        slv.finalprice as ahs_sku_level_price, -- ahs原始价
        case when slv.finalprice >= 4800 then slv.finalprice + 500
             when slv.finalprice >= 3000 then slv.finalprice + 420
             when slv.finalprice >= 2000 then slv.finalprice + 300
             when slv.finalprice >= 1200 then slv.finalprice + 200
             when slv.finalprice >= 800 then slv.finalprice + 150
             when slv.finalprice >= 400 then slv.finalprice + 70
             when slv.finalprice >= 200 then slv.finalprice + 40
                 else slv.finalprice
             end as ahs_couponed_price -- ahs卷后价
    from
    (
        select
            sum(t148.inquiry_amount_num) as "4036",
            t148."inquiry_key" as "151",
            t148."partition_date" as "5857",
            t148."product_sku_id" as "153",
            -- t180.product_sku_name,
            t148."product_level_id" as "154",
            -- t179.product_level_name,
            t148."trade_type_id" as "5124",
            t148."user_id" as "1983",
            t148."product_id" as "product_id",
            t134.product_name,
            t182."big_source_type_name" as "983"
        from
            dw.dw_source_recycle_inquiry as t148
        left join dim.dim_recycle_source_agent as t182 
            on t148."recycle_source_agent_id" = t182."recycle_source_agent_id"
        join dim.dim_product as t134 
            on t148."product_id" = t134."product_id"
        -- where t148."partition_date" between '2024-06-01' and '2024-06-16'
        where date(t148."partition_date") between (current_date + interval '-3' day) and current_date
            -- 2024-06-20 提取王鹏限定的用户询价订单明细
            AND t148."inquiry_key" in (
                select 
                    distinct inquiry_key 
                from insert.afl_user_recent_inquiry_key_table 
                where 
                    product_brand_name = '苹果'
                    and date(inquiry_date) = current_date + interval '-1' day
            )
            AND t134."product_category_parent_name" in ('手机')
            -- AND t134."product_category_parent_name" in ('笔记本')
            AND t148."trade_type_id" in (0)
            AND t182."big_source_type_name" in ('BD', '官网')
            AND case when (
                    (cast(t148.is_user_inquiry_flag_id as int)) = (0)
                  ) then ('是')
                  when (
                    (cast(t148.is_user_inquiry_flag_id as int)) = (1)
                  ) then ('否')
                end in ('是')
             AND t134.product_name in ('苹果 iPhone 13', '苹果 iPhone 13 Pro', '苹果 iPhone 13 Pro Max', '苹果 iPhone 14 Pro Max', '苹果 iPhone 14 Pro', '苹果 iPhone 14', '苹果 iPhone 15', '苹果 iPhone 15 Pro', '苹果 iPhone 15 Pro Max', '苹果 iPhone 12', '苹果 iPhone 12 Pro', '苹果 iPhone 12 Pro Max', '苹果 iPhone 11 Pro', '苹果 iPhone 11 Pro Max', '苹果 iPhone 11')
        group by
            t148."user_id",
            t148."trade_type_id",
            t148."partition_date",
            t148."inquiry_key",
            t148."product_sku_id",
            t148."product_id",
            t148."product_level_id",
            t134.product_name,
            t182."big_source_type_name"
    ) as t_origin
    left join
    (
        SELECT
            d.partition_date,
            d.product_sku_id,
            d.product_level_id,
            l.product_level_name, -- 等级名称
            d.finalprice,
            case when d.effect_competitiveprice is not NULL then d.effect_competitiveprice
            else d.finalprice end as jd_price
        FROM 
            dw.dw_source_daily_price d
        join 
            dim.dim_product_level l
        on d.product_level_id = l.product_level_id
        WHERE 
            d.ccgid = -1
            AND d.scgid IN (1)
            AND d.product_category_id = 1
            -- AND d.partition_date = '2024-06-18'
            AND date(d.partition_date) = current_date
        -- order by d.partition_date desc
    ) slv
    on t_origin."153" = slv.product_sku_id and t_origin."154" = slv.product_level_id and date(t_origin."5857") = date(slv.partition_date) + interval '-1' day
    left join 
    (
        select
            cvalue as val,
            cvalue_name as label
        from
            dim.dim_lookup
        where
            ctype = 'inquiry_trade_type_id'
    ) as t5124 
        on t_origin."5124" = t5124."val"
    left join 
        dim.dim_product_level as t179 
        on t_origin."154" = t179."product_level_id"
    left join 
        dim.dim_product_sku as t180 
        on t_origin."153" = t180."product_sku_id"
    left join 
    (
        select 
            * 
        from 
            ods.ods_new_inquiry_inquiry
        -- where partition_date between '2024-06-01' and '2024-06-16'
        where date(partition_date) between (current_date + interval '-3' day) and current_date
    ) as ods 
        on t_origin."151" = ods.key 
    -- order by 11 desc
"""


ZZ_YESTERDAY_PRICE_SQL = """
    select
      	ahs.product_sku_id,
        ahs.product_level_id,
      	zz.*
    from
    	ods.ods_stage_fact_crawl_platform_electronics_goods_info zz
    join 
    	-- 询价端按照询价次数排序TOP20000的ppv产品
    	insert.tmp_ahs_phone_ppv_details_top2w ahs
    on zz.activity_id = ahs.new_index
    where from_web = 'ZZ_recycle_price_v2'
        and date(get_day) = current_date + interval '-1' day
"""